package com.syni.mdd.yhd.common.all.dao.jpa;

import com.syni.mdd.yhd.common.all.dao.BaseJPADao;
import com.syni.mdd.yhd.common.all.entity.BmsDxGroupBuyUse;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

public interface BmsDxGroupBuyUseDao extends BaseJPADao<BmsDxGroupBuyUse,Integer> {
    List<BmsDxGroupBuyUse> findByOrderIdAndStatus(Integer orderId,Integer status);

    List<BmsDxGroupBuyUse> findByOrderIdIn(Set orderIds);

    List<BmsDxGroupBuyUse> findByOrderId(Integer orderId);

    /**自定义排序规则**/
    @Query(value = "select * from bms_dx_group_buy_use where order_id in (?1) ORDER BY FIELD(status,0,2,1)",nativeQuery = true)
    List<BmsDxGroupBuyUse> findByGroupCodeByMySort(Set orderIds);

    List<BmsDxGroupBuyUse> findByIdIn(Set ids);

    /** 统计订单类型券数 **/
    @Query(value = "select count(*) from bms_dx_group_buy_use bu where bu.order_id = ?1 " +
            "and bu.status = ?2",nativeQuery = true)
    Integer countOrderStatuNum(Integer orderId,Integer status);

    /** 统计订单类型券数 **/
    @Query(value = "select count(*) num from bms_dx_group_buy_use bu where bu.order_id = ?1 " +
            "and bu.status in (?2)",nativeQuery = true)
    Integer countOrderStatuNum(Integer orderId,Set status);

    /** 统计核销订单金额 **/
    @Query(value = "select IFNULL(sum(bu.once_shop_money),0) from bms_dx_group_buy_use bu " +
            "left join bms_dx_group_buy bg on bu.group_buy_id = bg.id " +
            "where bg.business_id = ?1 and bu.status in(?2)",nativeQuery = true)
    Double countGroupBuyUse(Integer businessId,Set status);

    /** 统计核销订单金额通过用户id **/
    @Query(value = "select IFNULL(sum(bu.gbuy_actual_consum) + sum(bu.discount_money),0) from bms_dx_group_buy_use bu " +
            "where bu.user_id = ?1 and bu.status =?2",nativeQuery = true)
    Double countGroupBuyUseByUserId(Integer userId,Integer status);

    @Query(value = "select count(bu.id) num,bu.order_id,bu.use_time from bms_dx_group_buy_use bu where bu.order_id in(?1) and " +
            "bu.status = ?2 group by bu.order_id,bu.use_time",nativeQuery = true)
    List<Map<String,Object>> countOnceOrderUserNum(Set orderIds,Integer status);

    /**根据时间范围统计核销订单数**/
    @Query(value = "select count(distinct bu.use_time) from bms_dx_group_buy_use bu " +
            "left join bms_dx_order bdo on bu.order_id = bdo.id " +
            "where bu.business_id in (?1) and bu.status in(?2) and bdo.order_type in (3,8) and " +
            "bu.use_time >= str_to_date(?3,?5) and bu.use_time <= str_to_date(?4,?5)",nativeQuery = true)
    Integer countGroupBuyUseCountByTime(Set businessId,Set status,String startTime,String endTime,String dateFormat);

    @Query(value = "select count(distinct bu.use_time) num,bu.business_id businessId from bms_dx_group_buy_use bu " +
            "left join bms_dx_order bdo on bu.order_id = bdo.id " +
            "where bu.business_id in (?1) and bu.status in(?2) and bdo.order_type in (3) " +
            "and bu.use_time >= str_to_date(?3,\"%Y%m%d%H%i%s\") and bu.use_time <= str_to_date(?4,\"%Y%m%d%H%i%s\") group by bu.business_id",nativeQuery = true)
    List<Map<String,Object>> countGroupBuyUseCountByBusinessIds(Set businessIds,Set status,String startTime,String endTime);


    /**根据时间范围统计核销订单金额**/
    @Query(value = "select IFNULL(sum(bu.once_shop_money),0) from bms_dx_group_buy_use bu " +
            "left join bms_dx_order bdo on bu.order_id = bdo.id " +
            "where bu.business_id in(?1) and bu.status in(?2) and bdo.order_type in (3,8) and " +
            "bu.use_time >= str_to_date(?3,?5) and bu.use_time <= str_to_date(?4,?5)",nativeQuery = true)
    Double countGroupBuyUseSumByTime(Set businessId,Set status,String startTime,String endTime,String dateFormat);

    /**根据时间范围查询核销列表**/
    @Query(value = "select * from bms_dx_group_buy_use bu inner join bms_dx_order bdo " +
            "on bu.order_id = bdo.id where bdo.bms_business_id = ?1 and bdo.order_type in(?2) and bu.status in(?3) and " +
            "bu.use_time >= str_to_date(?4,\'%Y%m%d%H%i%s\') and bu.use_time <= str_to_date(?5,\'%Y%m%d%H%i%s\') " +
            "group by bu.use_time order by bu.use_time desc",nativeQuery = true)
    List<BmsDxGroupBuyUse> getGroupBuyUseListByTime(Integer businessId, Set orderTypes, Set status, String startTime,
                                                    String endTime, Pageable pageable);

    /**根据时间范围查询核销列表**/
    @Query(value = "select * from bms_dx_group_buy_use bu inner join bms_dx_order bdo " +
            "on bu.order_id = bdo.id where bdo.bms_business_id = ?1 and bdo.order_type in(?2) and bu.status in(?3) " +
            "group by bu.use_time order by bu.use_time desc",nativeQuery = true)
    List<BmsDxGroupBuyUse> getGroupBuyUseList(Integer businessId, Set orderTypes, Set status,Pageable pageable);

    /**根据时间范围查询核销列表**/
    @Query(value = "select count(DISTINCT bu.use_time) from bms_dx_group_buy_use bu inner join bms_dx_order bdo " +
            "on bu.order_id = bdo.id where bdo.bms_business_id = ?1 and bdo.order_type in(?2) and bu.status in(?3)",nativeQuery = true)
    Integer getGroupBuyUseList(Integer businessId, Set orderTypes, Set status);

    /** 查询退款信息 **/
    @Query(value = "select sum(bu.group_buy_price) refundMoney,bu.id,bu.commit_time commitTime,bu.audit_time auditTime,bu.approved_time approvedTime," +
            "bu.refund_time refundTime,bu.accept_time acceptTime from bms_dx_group_buy_use " +
            "bu where bu.order_id = ?1 and bu.status = ?2 and bu.audit_time = ?3 and bu.refund_time = ?4",nativeQuery = true)
    List<Map<String,Object>> countRefundGroupBuy(Integer orderId, Integer status, Date auditTime,Date refundTime);

    /**统计用户用过多少次团购(一次核销多张算一次)**/
    @Query(value = "select count(DISTINCT bu.use_time) num from bms_dx_group_buy_use bu " +
            "where bu.status = 1 and bu.user_id = ?1 ",nativeQuery = true)
    Integer countUserUseGroupBuyNum(Integer userId);

    @Query(value = "select count(DISTINCT bu.id) num from bms_dx_group_buy_use bu " +
            "where bu.status in (0,1) and bu.user_id = ?1 and bu.group_buy_id = ?2",nativeQuery = true)
    Integer countUserBuyGroupBuyNum(Integer userId, Integer id);

    @Query(value = "select sum(bu.gbuy_actual_consum) gac,sum(bu.discount_money) dm,bg.business_id businessId from bms_dx_group_buy_use bu " +
            "left join bms_dx_group_buy bg on bu.group_buy_id = bg.id " +
            "where bu.order_id in(?1) and bu.status in(1) group by bg.business_id",nativeQuery = true)
    List<Map<String,Object>>  countActivityGroupBuy(Set<Integer> orderIds);

    @Query(value = "select sum(bu.gbuy_actual_consum) gac,sum(bu.discount_money) dm,sum(bu.once_shop_money) sm,bdo.order_no as orderNo from bms_dx_group_buy_use bu " +
            "left join bms_dx_order bdo on bu.order_id = bdo.id " +
            "where bu.order_id = ?1 and unix_timestamp(bu.use_time) = ?2",nativeQuery = true)
    Map<String,Object> countMoneyData(Integer orderId, Integer useTime);

    BmsDxGroupBuyUse findByGroupBuyCode(String groupBuyCode);
}
